package com.demo.mapper;

import com.demo.dto.PartnerSelfDTO;
import com.demo.model.FormsInfo;
import com.demo.model.PartnerStat;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.List;
@Repository
public interface PartnerStatMapper {
    @Select("SELECT sys_user.user_id ,sys_user.true_name ,sys_user.dep_id ,sys_department.dep_name ,\n" +
            "            sum(CASE WHEN crm_temp.partner_type =1 THEN 1 ELSE 0 END) assetNum,\n" +
            "            sum(CASE WHEN crm_temp.partner_type =2 THEN 1 ELSE 0 END) fundNum,\n" +
            "            sum(CASE WHEN crm_temp.partner_type =3 THEN 1 ELSE 0 END) exitNum,\n" +
            "            sum(CASE WHEN crm_temp.partner_type =4 THEN 1 ELSE 0 END) justiceNum,\n" +
            "           sum(CASE WHEN crm_temp.partner_type =5 THEN 1 ELSE 0 END) peerNum,\n" +
            "           sum(CASE WHEN crm_temp.partner_type =6 THEN 1 ELSE 0 END) orgNum\n" +
            "            FROM sys_user left join (select sys_user.user_id ,sys_user.true_name,sys_user.dep_id ,sys_department.dep_name,\n" +
            "            crm_partner.partner_type from sys_user \n" +
            "                  left join crm_partner on sys_user.user_id =crm_partner.create_user_id\n" +
            "                  inner join sys_department on sys_user.dep_id =sys_department.id\n" +
            "                  where sys_user.role_id!='002' and sys_user.disabled=0 and crm_partner .disabled=0 \n" +
            "                  and crm_partner.create_time >=#{begin} \n" +
            "                 and crm_partner.create_time <#{end}) crm_temp on sys_user.user_id =crm_temp.user_id inner join sys_department on\n" +
            "                 sys_user.dep_id=sys_department.id   where sys_user.disabled =0 and sys_user.role_id!='002'\n" +
            "            group by sys_user.user_id\n" +
            "            order by sys_user.dep_id,sys_user.user_id")
    List<PartnerStat> stat(@Param("begin") Date begin, @Param("end") Date end);


    @Select("SELECT user_id,true_name ,dep_id,dep_name,\n" +
            "sum(CASE WHEN partner_type =1 THEN 1 ELSE 0 END) assetNum,\n" +
            "sum(CASE WHEN partner_type =2 THEN 1 ELSE 0 END) fundNum,\n" +
            "sum(CASE WHEN partner_type =3 THEN 1 ELSE 0 END) exitNum,\n" +
            "sum(CASE WHEN partner_type =4 THEN 1 ELSE 0 END) justiceNum,\n" +
            "sum(CASE WHEN partner_type =5 THEN 1 ELSE 0 END) peerNum,\n" +
            "sum(CASE WHEN partner_type =6 THEN 1 ELSE 0 END) orgNum\n" +
            "FROM (select sys_user.user_id ,sys_user.true_name,sys_user.dep_id ,sys_department.dep_name,\n" +
            "crm_partner.partner_type from sys_user \n" +
            "      left join crm_partner on sys_user.user_id =crm_partner.create_user_id \n" +
            "      inner join sys_department on sys_user.dep_id =sys_department.id\n" +
            "      where sys_user.disabled=0 and crm_partner .disabled=0 \n" +
            "      and crm_partner.create_time >=#{begin} " +
            "      and crm_partner.create_time <#{end}) temp\n" +
            "where dep_id=#{depId}" +
            "group by user_id\n" +
            "order by dep_id,user_id")
    List<PartnerStat> statSum(@Param("begin") Date begin, @Param("end") Date end,String depId);

    /**
     * 查询所有人数的id排序
     * @return
     */
    @Select("SELECT sys_user.dep_id as depId,sys_user .user_id as userId," +
            "sys_user .true_name as trueName ,sys_department.dep_name as depName" +
            " from sys_user left join sys_department on sys_user.dep_id =sys_department.id " +
            "where  sys_user .role_id !='002' and sys_user.disabled =0 order BY  sys_user .dep_id ")
    List<FormsInfo> all();
    /**
     * 本周新增人数
     * @return
     */
    @Select("select a.user_id as userId,a.true_name ,c.dep_name ,COUNT(DISTINCT b.name ,b.telephone ,b.telephone_list ,b.weixin ,b.email ) as week from\n" +
            "sys_user a left join (select * from crm_partner " +
            "where crm_partner.disabled=0 and\n" +
            "create_time >= #{ben} and create_time < #{end})  b " +
            "on a.user_id =b.create_user_id left join sys_department c on a.dep_id =c.id \n" +
            "where a.disabled =0 and a.role_id !='002'\n" +
            " group by userId ,true_name,dep_name")
    List<FormsInfo> week(String ben,String end);

    @Select("select * from sys_user where disabled =0 and role_id !='002'")
    List<FormsInfo> personal();

    /**
     * 本月新增人数
     * @return
     */
    @Select("select a.user_id as userId,a.true_name ,c.dep_name ,COUNT(DISTINCT b.name ,b.telephone ,b.telephone_list ,b.weixin ,b.email ) as month from\n" +
            "sys_user a left join (select * from crm_partner " +
            "where crm_partner.disabled=0 and\n" +
            "DATE_FORMAT( create_time, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' ))  " +
            "b on a.user_id =b.create_user_id left join sys_department c on a.dep_id =c.id \n" +
            "where a.disabled =0 and a.role_id !='002'\n" +
            " group by userId ,true_name,dep_name")
    List<FormsInfo> month();

    /**
     * 本季度累计人数
     * @return
     */
    @Select("select a.user_id as userId,a.true_name ,c.dep_name ,COUNT(DISTINCT b.name ,b.telephone ,b.telephone_list ,b.weixin ,b.email ) as quarter from\n" +
            "sys_user a left join (select * from crm_partner where crm_partner.disabled=0 " +
            "and\n" +
            "QUARTER(create_time ) = QUARTER(NOW()))  b on a.user_id =b.create_user_id " +
            "left join sys_department c on a.dep_id =c.id \n" +
            "where a.disabled =0 and a.role_id !='002'\n" +
            " group by userId ,true_name,dep_name")
    List<FormsInfo> quarter();

    /**
     * 本年度新增人数
     * @return
     */
    @Select("select a.user_id as userId,a.true_name ,c.dep_name ,COUNT(DISTINCT b.name ,b.telephone ,b.telephone_list ,b.weixin ,b.email ) as year from\n" +
            "sys_user a left join (select * from crm_partner where crm_partner.disabled=0 and\n" +
            "YEAR(create_time )=YEAR(NOW()))  b on a.user_id =b.create_user_id " +
            "left join sys_department c on a.dep_id =c.id \n" +
            "where a.disabled =0 and a.role_id !='002'\n" +
            " group by userId ,true_name,dep_name")
    List<FormsInfo> year();


    /**
     * 查询用户季度目标
     * @param date
     * @return
     */
    @Select("SELECT user_aim.* \n" +
            "from sys_user inner join sys_department on sys_user.dep_id =sys_department.id \n" +
            "inner join user_aim on sys_user.user_id =user_aim .user_id\n" +
            "where  sys_user .role_id !='002' and sys_user.disabled =0" +
            " and YEAR(user_aim.create_date)=#{date} order BY  sys_user .dep_id")
    List<FormsInfo> aimAll(String date);

    /**
     * 查询所有部门id
     * @return
     */
    @Select("select id as depId,dep_name as depName from sys_department ")
    List<FormsInfo> depId();

    /**
     * 录入客户的数量
     * @return
     */
    @Select(" select a.user_id as userId,a.true_name ,c.dep_name ,COUNT(DISTINCT b.name ,b.telephone ,b.telephone_list ,b.weixin ,b.email ) as allClient from sys_user a \n" +
            "           left join (select * from crm_partner where disabled =0) b " +
            "on a.user_id =b.create_user_id left join sys_department c on a.dep_id =c.id \n" +
            "                      where a.disabled =0  and a.role_id !='002'\n" +
            "                    group by userId")
    List<FormsInfo> allClient();


    /**
     * 录入伙伴数量
     * @return
     */
    @Select(" SELECT sys_user.user_id as userId,sys_user.true_name as trueName,COUNT(*) as allInfo from  sys_user left join \n" +
            "(select * from crm_partner where disabled =0) crm_partner " +
            "on crm_partner.create_user_id =sys_user.user_id\n" +
            "left join sys_department on sys_user.dep_id =sys_department.id where\n" +
            "             sys_user.disabled=0  and sys_user.role_id!='002'\n" +
            "                        GROUP by user_id\n" +
            "                                  order by dep_id,user_id ")
    List<FormsInfo> allInfo();


    @Select("select sys_department.dep_name, tj.* from (\n" +
            "select  dep_id,true_name, max(case partner_type when 6 then num else 0 end) gov,\n" +
            "max(case partner_type when 5 then num else 0 end) peer,\n" +
            "max(case partner_type when 4 then num else 0 end) justice,\n" +
            "max(case partner_type when 3 then num else 0 end) exits,\n" +
            "max(case partner_type when 2 then num else 0 end) fund,\n" +
            "max(case partner_type when 1 then num else 0 end) asset\n" +
            "from \n" +
            "(\n" +
            "SELECT crm_partner.partner_type,sys_user.user_id,sys_user.dep_id,sys_user.true_name ,count(crm_partner.create_time) num \n" +
            "from sys_user left join \n" +
            "(select * from crm_partner where crm_partner.disabled=0 and \n" +
            "create_time>=#{begin} and create_time<#{end}) crm_partner\n" +
            "on sys_user.user_id=crm_partner.create_user_id  where sys_user.disabled=0 and sys_user.role_id!='002'\n" +
            "group by crm_partner.partner_type,sys_user.user_id,sys_user.dep_id,sys_user.true_name ) temp\n" +
            "group by user_id,dep_id,true_name) tj INNER join sys_department on tj.dep_id=sys_department.id \n" +
            "order by  dep_id")
    List<PartnerSelfDTO> partnerWeek(String begin,String end);

    @Select("SELECT sys_user.user_id,sys_user.dep_id,sys_user.true_name ,COUNT(distinct crm_partner.name,crm_partner.telephone_list,crm_partner.telephone ,crm_partner.weixin,crm_partner.email) counts \n" +
            "from sys_user left join \n" +
            "(select * from crm_partner where crm_partner.disabled=0 and \n" +
            "create_time>=#{begin} and create_time<#{end}) crm_partner\n" +
            "on sys_user.user_id=crm_partner.create_user_id  where sys_user.disabled=0 and sys_user.role_id!='002' \n" +
            "group by sys_user.user_id,sys_user.dep_id,sys_user.true_name\n" +
            "order by  dep_id")
    List<PartnerSelfDTO> partnerWeekCount(String begin,String end);

    @Select("select sys_department.dep_name, tj.* from (\n" +
            "select  dep_id,true_name, max(case partner_type when 6 then num else 0 end) gov,\n" +
            "max(case partner_type when 5 then num else 0 end) peer,\n" +
            "max(case partner_type when 4 then num else 0 end) justice,\n" +
            "max(case partner_type when 3 then num else 0 end) exits,\n" +
            "max(case partner_type when 2 then num else 0 end) fund,\n" +
            "max(case partner_type when 1 then num else 0 end) asset\n" +
            "from \n" +
            "(\n" +
            "SELECT crm_partner.partner_type,sys_user.user_id,sys_user.dep_id,sys_user.true_name ,count(crm_partner.create_time) num \n" +
            "from sys_user left join \n" +
            "(select * from crm_partner where crm_partner.disabled=0 and \n" +
            "year(crm_partner.create_time)=#{year} and month(crm_partner.create_time)=#{mouth}) crm_partner\n" +
            "on sys_user.user_id=crm_partner.create_user_id  where sys_user.disabled=0 and sys_user.role_id!='002'\n" +
            "group by crm_partner.partner_type,sys_user.user_id,sys_user.dep_id,sys_user.true_name ) temp\n" +
            "group by user_id,dep_id,true_name) tj INNER join sys_department on tj.dep_id=sys_department.id \n" +
            "order by  dep_id\n")

    List<PartnerSelfDTO> partnerMonth(String year,String mouth);

    @Select("SELECT sys_user.user_id,sys_user.dep_id,sys_user.true_name ,COUNT(distinct crm_partner.name,crm_partner.telephone_list,crm_partner.telephone ,crm_partner.weixin,crm_partner.email) counts \n" +
            "from sys_user left join \n" +
            "(select * from crm_partner where crm_partner.disabled=0 and \n" +
            "year(crm_partner.create_time)=#{year} and month(crm_partner.create_time)=#{mouth}) crm_partner\n" +
            "on sys_user.user_id=crm_partner.create_user_id  where sys_user.disabled=0 and sys_user.role_id!='002' \n" +
            "group by sys_user.user_id,sys_user.dep_id,sys_user.true_name\n" +
            "order by  dep_id")
    List<PartnerSelfDTO> partnerMonthCount(String year,String mouth);

    @Select("select sys_department.dep_name, tj.* from (\n" +
            "select  dep_id,true_name, max(case partner_type when 6 then num else 0 end) gov,\n" +
            "max(case partner_type when 5 then num else 0 end) peer,\n" +
            "max(case partner_type when 4 then num else 0 end) justice,\n" +
            "max(case partner_type when 3 then num else 0 end) exits,\n" +
            "max(case partner_type when 2 then num else 0 end) fund,\n" +
            "max(case partner_type when 1 then num else 0 end) asset\n" +
            "from \n" +
            "(\n" +
            "SELECT crm_partner.partner_type,sys_user.user_id,sys_user.dep_id,sys_user.true_name ,count(crm_partner.create_time) num \n" +
            "from sys_user left join \n" +
            "(select * from crm_partner where crm_partner.disabled=0 and year(crm_partner.create_time)=#{year} and \n" +
            "QUARTER(crm_partner.create_time )=#{quar}) crm_partner\n" +
            "on sys_user.user_id=crm_partner.create_user_id  where sys_user.disabled=0 and sys_user.role_id!='002'\n" +
            "group by crm_partner.partner_type,sys_user.user_id,sys_user.dep_id,sys_user.true_name ) temp\n" +
            "group by user_id,dep_id,true_name) tj INNER join sys_department on tj.dep_id=sys_department.id \n" +
            "order by  dep_id"
            )
    List<PartnerSelfDTO> partnerQuarter(String year,Integer quar);

    @Select("SELECT sys_user.user_id,sys_user.dep_id,sys_user.true_name ,COUNT(distinct crm_partner.name,crm_partner.telephone_list,crm_partner.telephone ,crm_partner.weixin,crm_partner.email) counts \n" +
            "from sys_user left join \n" +
            "(select * from crm_partner where crm_partner.disabled=0 and year(crm_partner.create_time)=#{year} and \n" +
            "QUARTER(crm_partner.create_time )=#{quar}) crm_partner\n" +
            "on sys_user.user_id=crm_partner.create_user_id  where sys_user.disabled=0 and sys_user.role_id!='002' \n" +
            "group by sys_user.user_id,sys_user.dep_id,sys_user.true_name\n" +
            "order by  dep_id")
    List<PartnerSelfDTO> partnerQuarterCount(String year,Integer quar);



}
